package com.cindata

import com.cindata.dictionary.BuildingTypeDict
import com.cindata.dictionary.DataDict
import com.cindata.dictionary.EstateStatusDict
import com.cindata.dictionary.HouseNatureDict
import grails.converters.JSON
import grails.gorm.transactions.Transactional
import org.springframework.security.access.annotation.Secured

/**
 * 银监预警 控制器
 */
@Secured(['isAuthenticated()'])
@Transactional
class BankingRegulatoryController {

    def springSecurityService
    def dataSource

    def index() {
//        def user = User.findById(springSecurityService.principal.id)
//        def defaultCity = user?.account?.city?.zoneNumber
////        def zoneNumber = params["city"] ?: defaultCity
//        def zoneNumber = '410100'
//        def provinceZoneNumber = '440000'
//        def db = new groovy.sql.Sql(dataSource)
//
//        def sqlProvince = " select new map(count(e.id) as counts, (select a.name from Area a where id = e.city.id) as cityName) from Estate e where e.province.zoneNumber = '${provinceZoneNumber}' " + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " and e.estateStatus.code = '1' group by e.city.id order by counts desc"
//        def provinceHouse = Estate.executeQuery(sqlProvince).asList()
//        def cityCount = provinceHouse.size()
//        def estateCounts = 0
//        provinceHouse?.each {
//            if (it?.counts && it?.counts > 0) {
//                estateCounts += it?.counts
//            }
//        }
//        def cityName1 = provinceHouse[0]?.cityName
//        def cityEstateCounts1 = provinceHouse[0]?.counts ? provinceHouse[0]?.counts : 0
//        def cityName2 = provinceHouse[cityCount - 1]?.cityName
//        def cityEstateCounts2 = provinceHouse[cityCount - 1]?.counts ? provinceHouse[cityCount - 1]?.counts : 0
//        println "provinceHouse:  " + provinceHouse
//
//        def sqlcltv = " select new map(sum(e.loansBalance)/sum(e.loansEvaluateTotalPrice) as cltv, (select a.name from Area a where id = e.city.id) as cityName) from Estate e where e.province.zoneNumber = '${provinceZoneNumber}' and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " and e.estateStatus.code = '1' group by e.city.id"
//        def sqlcltv1 = " select sum(e.loansBalance)/sum(e.loansEvaluateTotalPrice) as cltv from Estate e where e.province.zoneNumber = '${provinceZoneNumber}' and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " and e.estateStatus.code = '1'"
//        def cltvList = Estate.executeQuery(sqlcltv).asList()
//        def cltv = Estate.executeQuery(sqlcltv1)
//        println "sqlcltv:  " + sqlcltv
//        println "cltvList:  " + cltvList
//        println "cltv:  " + cltv
//
//        //住宅的新房、二手房押品分布
//        def houseNature1 = HouseNatureDict.findByCode("FistHouse")?.id
//        def houseNature2 = HouseNatureDict.findByCode("SecondHouse")?.id
//        def buildingType = BuildingTypeDict.findByCode("Residential")?.id
//        def sql1 = " select new map(count(e.id) as counts, (select a.name from Area a where id = e.district.id) as districtName) " +
//                "from Estate e where e.city.zoneNumber = '${zoneNumber}' " +
//                " and e.houseNature.id = ${houseNature1}  " +
//                " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) +
//                " and e.estateStatus.code = '1' and e.buildingType.id =${buildingType} group by e.district.id"
//        def sql2 = "select new map(count(e.id) as counts, (select a.name from Area a where id = e.district.id) as districtName)" +
//                " from Estate e where e.city.zoneNumber = '${zoneNumber}' " + " and e.houseNature.id = ${houseNature2}  " +
//                " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " and e.estateStatus.code = '1' and e.buildingType.id =${buildingType} group by e.district.id"
//        def sqlnew = sql1 + " order by counts desc"
//        def sqlSecond = sql2 + " order by counts desc"
//        def sqldirect1 = sql1 + " order by districtName desc"
//        def sqldirect2 = sql2 + " order by districtName desc"
//        def newDirect = Estate.executeQuery(sqldirect1).asList()
//        def secDirect = Estate.executeQuery(sqldirect2).asList()
//        def direct = ""
//        def newData = ""
//        def secData = ""
//        newDirect?.each {
//            direct = direct + it?.districtName + ','
//            newData = newData + it?.counts + ','
//        }
//        secDirect?.each {
//            secData = newData + it?.counts + ','
//        }
//        direct = direct.substring(0, direct.lastIndexOf(","))
//        newData = newData.substring(0, newData.lastIndexOf(","))
//        secData = secData.substring(0, secData.lastIndexOf(","))
////        println "direct:  "+direct
////        println "newData:  "+newData
////        println "secData:  "+secData
//        def newHouse = Estate.executeQuery(sqlnew).asList()
//        def secHouse = Estate.executeQuery(sqlSecond).asList()
//        def newCounts = 0
//        newHouse?.each {
//            if (it?.counts && it?.counts > 0) {
//                newCounts += it?.counts
//            }
//        }
//        def districtName1 = newHouse[0]?.districtName
//        def districtEstateCounts1 = newHouse[0]?.counts ? newHouse[0]?.counts : 0
//
//        def secCounts = 0
//        secHouse?.each {
//            if (it?.counts && it?.counts > 0) {
//                secCounts += it?.counts
//            }
//        }
//        def districtName2 = secHouse[0]?.districtName
//        def districtEstateCounts2 = secHouse[0]?.counts ? secHouse[0]?.counts : 0
////        println "newHouse:  "+newHouse
////        println "secHouse:  "+secHouse
//
////        商业押品分布
//        def businessBuild = BuildingTypeDict.findByCode("Business")?.id
//        def sql3 = " select new map(count(e.id) as counts, (select a.name from Area a where id = e.district.id) as districtName) from Estate e where e.city.zoneNumber = '${zoneNumber}' and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " and e.estateStatus.code = '1' and e.buildingType.id =" + businessBuild + " group by e.district.id"
//        def sqlbusiness = sql3 + " order by counts desc"
//        def sqlbusinessData = sql3 + " order by districtName desc"
////        println "sqlbusiness: "+sqlbusiness
//        def businessHouse = Estate.executeQuery(sqlbusiness).asList()
//        def businessHouseData = Estate.executeQuery(sqlbusinessData).asList()
//        def businessCounts = 0
//        businessHouse?.each {
//            if (it?.counts && it?.counts > 0) {
//                businessCounts += it?.counts
//            }
//        }
//        def businessData = ""
//        businessHouseData?.each {
//            businessData = businessData + it?.counts + ","
//        }
//        businessData = businessData.substring(0, businessData.lastIndexOf(","))
//        def districtName3 = businessHouse[0]?.districtName
//        def districtName4 = businessHouse[1]?.districtName
//        def districtName5 = businessHouse[2]?.districtName
//        def districtEstateCounts3 = businessHouse[0]?.counts ? businessHouse[0]?.counts : 0
//        def districtEstateCounts4 = businessHouse[1]?.counts ? businessHouse[1]?.counts : 0
//        def districtEstateCounts5 = businessHouse[2]?.counts ? businessHouse[2]?.counts : 0
////        println "businessHouse:  "+businessHouse
//
//
////        住宅价格及价差
//        def sqlNewPrice = " select new map(avg(loansEvaluatePrice) as unitPrice, " +
//                "(select a.name from Area a where id = e.district.id) as districtName) " +
//                "from Estate e where e.city.zoneNumber = '${zoneNumber}' and e.account.id in " +
//                Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) +
//                " and e.estateStatus.code = '1' and e.buildingType.id =${buildingType} and" +
//                " e.houseNature.id = ${houseNature1} group by e.district.id order by e.district.id desc "
//        def sqlSecPrice = "select new map(avg(loansEvaluatePrice) as unitPrice, (select a.name from Area a where id = e.district.id) as districtName) from Estate e where e.city.zoneNumber = '${zoneNumber}' and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " and e.estateStatus.code = '1' and e.buildingType.id =${buildingType} and e.houseNature.id = ${houseNature2} group by e.district.id order by e.district.id desc"
//        def newPriceList = Estate.executeQuery(sqlNewPrice).asList()
//        def secPriceList = Estate.executeQuery(sqlSecPrice).asList()
////        println"sqlNewPrice:  "+sqlNewPrice
////        println"sqlSecPrice:  "+sqlSecPrice
////        println "newPriceList:  "+newPriceList
////        println "secPriceList:  "+secPriceList
//        respond provinceHouse, model: [provinceHouse: provinceHouse, estateCounts: estateCounts, cityName1: cityName1, cityEstateCounts1: cityEstateCounts1, cityName2: cityName2, cityEstateCounts2: cityEstateCounts2, cltvList: cltvList, cltv: cltv,
//                                       direct       : direct, newData: newData, newCounts: newCounts, districtName1: districtName1, districtEstateCounts1: districtEstateCounts1, secData: secData, secCounts: secCounts, districtName2: districtName2, districtEstateCounts2: districtEstateCounts2, businessData: businessData, businessCounts: businessCounts,
//                                       districtName3: districtName3, districtName4: districtName4, districtName5: districtName5, districtEstateCounts3: districtEstateCounts3, districtEstateCounts4: districtEstateCounts4, districtEstateCounts5: districtEstateCounts5,]

    }


    /**
     * 季度时间轴数据*/
    @Transactional
    def timesLineForQuarter(int yearLength) {
        //1:一年 2:两年
        Calendar a = Calendar.getInstance()
        //当前年
        int endYear = a.get(Calendar.YEAR)
        //当前月
        int endMonth = a.get(Calendar.MONTH) + 1
        //开始日期
        a.add(Calendar.MONTH, -(yearLength * 12 - 1))
        int startYear = a.get(Calendar.YEAR)
        int startMonth = a.get(Calendar.MONTH) + 1
        def quarterList = new ArrayList()
        try {
            def list = getQuarterList(startYear, startMonth, endYear, endMonth)
            //去掉重复
            for (
                    int i = 0;
                    i < list.size();
                    i++) {
                String str = list.get(i)
                if (!quarterList.contains(str)) {
                    quarterList.add(str)
                }
            }
        }
        catch (Exception e) {
            e.printStackTrace()
        }
        return quarterList
    }

    def getQuarterList(int startYear, int startMonth, int endYear, int endMonth) {
        def quarterList = new ArrayList()
        if (endYear - startYear == 0) {
            for (
                    int i = startMonth;
                    i <= endMonth;
                    i++) {
                quarterList.add(getQuarterStr(startYear, i))
            }
        } else if (endYear - startYear == 1) {
            for (
                    int i = startMonth;
                    i <= 12;
                    i++) {
                quarterList.add(getQuarterStr(startYear, i))
            }
            for (
                    int i = 1;
                    i <= endMonth;
                    i++) {
                quarterList.add(getQuarterStr(endYear, i))
            }
        } else if (endYear - startYear == 2) {
            for (
                    int i = startMonth;
                    i <= 12;
                    i++) {
                quarterList.add(getQuarterStr(startYear, i))
            }
            for (
                    int i = 1;
                    i <= 12;
                    i++) {
                quarterList.add(getQuarterStr(startYear + 1, i))
            }
            for (
                    int i = 1;
                    i <= endMonth;
                    i++) {
                quarterList.add(getQuarterStr(endYear, i))
            }
        } else {
            for (
                    int i = startMonth;
                    i <= 12;
                    i++) {
                quarterList.add(getQuarterStr(startYear, i))
            }
            for (
                    int i = startYear + 1;
                    i <= endYear - 1;
                    i++) {
                for (
                        int j = 1;
                        j <= 12;
                        j++) {
                    quarterList.add(getQuarterStr(i, j))
                }
            }
            for (
                    int i = 1;
                    i <= endMonth;
                    i++) {
                quarterList.add(getQuarterStr(endYear, i))
            }
        }
        return quarterList
    }

    def getQuarterStr(int year, int month) {
        def quarterStr = ""
        switch (month) {
            case 1:
            case 2:
            case 3:
                quarterStr += year + "一季度"
                break
            case 4:
            case 5:
            case 6:
                quarterStr += year + "二季度"
                break
            case 7:
            case 8:
            case 9:
                quarterStr += year + "三季度"
                break
            case 10:
            case 11:
            case 12:
                quarterStr += year + "四季度"
                break
        }
        return quarterStr
    }

//    抵押率监测 不用了
    @Transactional
    def historyCltvData() {
        def user = User.findById(springSecurityService.principal.id)
        def defaultCity = user?.account?.city?.zoneNumber
//        def zoneNumber = params["city"] ?: defaultCity
        def zoneNumber = '410100'
        def yearLength = params['yearLength'] ? Integer.parseInt(params['yearLength']) : 2
        def resultHouse = new ArrayList()
        def resultBusiness = new ArrayList()
        def resultList = new ArrayList()
        def status = ""
        def buildingType = BuildingTypeDict.findByCode("Residential")?.id
        def businessBuild = BuildingTypeDict.findByCode("Business")?.id
        //抵押率监测

        try {
            def quarterList = timesLineForQuarter(yearLength)
            for (String yearQuarter : quarterList) {
                def cltvSql = "select new map(sum(e.loansBalance)/sum(e.latestEvaluatePrice) as cltv, " +
                        "(select a.name from Area a where id = e.district.id) as districtName) " +
                        "from Estate e where e.city.zoneNumber = '${zoneNumber}'" +
                        " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) +
                        " and e.estateStatus.code = '1'"

                def year = yearQuarter.substring(0, 4)
                def quarter = yearQuarter.substring(4)
                if (quarter == '一季度') {
                    def startTime = year + "-01-01 00:00:00"
                    def endTime = year + "-03-31 23:59:59"
                    cltvSql += " and to_char(e.startDate,'YYYY-MM-DD HH24:MI:SS') >='${startTime}' and to_char(e.startDate,'YYYY-MM-DD HH24:MI:SS') <= '${endTime}'"
                } else if (quarter == '二季度') {
                    def startTime = year + "-04-01 00:00:00"
                    def endTime = year + "-06-30 23:59:59"
                    cltvSql += " and to_char(e.startDate,'YYYY-MM-DD HH24:MI:SS') >='${startTime}' and to_char(e.startDate,'YYYY-MM-DD HH24:MI:SS') <= '${endTime}'"
                } else if (quarter == '三季度') {
                    def startTime = year + "-07-01 00:00:00"
                    def endTime = year + "-09-30 23:59:59"
                    cltvSql += " and to_char(e.startDate,'YYYY-MM-DD HH24:MI:SS') >='${startTime}' and to_char(e.startDate,'YYYY-MM-DD HH24:MI:SS') <= '${endTime}'"
                } else if (quarter == '四季度') {
                    def startTime = year + "-10-01 00:00:00"
                    def endTime = year + "-12-31 23:59:59"
                    cltvSql += " and to_char(e.startDate,'YYYY-MM-DD HH24:MI:SS') >='${startTime}' and to_char(e.startDate,'YYYY-MM-DD HH24:MI:SS') <= '${endTime}'"
                }

                def sqlhousecltv = cltvSql + " and e.buildingType.id =${buildingType} group by e.district.id order by districtName desc"
                def sqlbusinesscltv = cltvSql + " and e.buildingType.id =${businessBuild} group by e.district.id order by districtName desc"
                resultHouse = Estate.executeQuery(sqlhousecltv).asList()
                resultBusiness = Estate.executeQuery(sqlbusinesscltv).asList()
                def cltvData = []
                for (def i = 0; i < resultHouse.size(); i++) {
//                    println "resultHouse:   "+resultHouse[i]
                    def map = new HashMap()
                    map.put("directData", resultHouse[i].districtName)
                    map.put("cltvHouseData", resultHouse[i].cltv ?: 0)
                    if (resultBusiness[i] && resultBusiness[i].districtName == resultHouse[i].districtName) {
                        map.put("cltvBusinessData", resultBusiness[i].cltv ?: 0)
                    } else {
                        map.put("cltvBusinessData", 0)
                    }
                    cltvData.add(map)
                }
                def map = new HashMap()
                map.put("YEARQUARTER", yearQuarter)
                map.put("cltvData", cltvData)

                resultList.add(map)
            }
            status = "success"

        }
        catch (Exception e) {
            e.printStackTrace()
            status = "false"
        }
//        println "historyCltvData  resultList:  "+resultList
        render([resultList: resultList, status: status] as JSON)
    }
    /**
     * 住宅押品分布
     * @return
     */
    def messuage() {
        def bank = Account.findById(params?.bank)
        def city = City.findByZoneNumber(params?.city)
        def province = Province.findByName("广东省")
        def fistHouse = HouseNatureDict.findByCode("FistHouse")
        def secondHouse = HouseNatureDict.findByCode("SecondHouse")
        def buildtype = BuildingTypeDict.findByCode("Residential")
        def sql = "SELECT\n"
        if (city) {
            sql += "( SELECT NAME FROM area WHERE area_id = t1.district_id ) AS distrcit_name,"
        } else {
            sql += "( SELECT NAME FROM area WHERE area_id = t1.city_id ) AS distrcit_name,"
        }
        sql += "	t1.COUNT,\n" +
                "	t2.COUNT AS count1 \n" +
                "FROM\n" +
                "	(\n" +
                "SELECT \n"
        if (city) {
            sql += "	c.district_id,\n"
        } else {
            sql += "	c.city_id,\n"
        }

        sql += "	COUNT ( * ) \n" +
                "FROM\n" +
                "	collateral\n" +
                "	C LEFT JOIN estate e ON C.estate_id = e.ID \n" +
                "WHERE\n" +
                "	building_type_id = ${buildtype?.id} \n"
        if (city) {
            sql += "	AND C.city_id = ${city?.id} \n"
        } else {
            sql += "	AND C.province_id = ${province?.id} \n"
        }
        if (bank) {
            sql += "	AND C.account_id = ${bank?.id} \n"
        }
        sql += "	AND house_nature_id = ${fistHouse?.id} \n" +
                "GROUP BY\n"
        if (city) {
            sql += "	C.district_id \n"
        } else {
            sql += "	C.city_id \n"
        }
        sql += "	) t1\n" +
                "	LEFT JOIN (\n" +
                "SELECT \n"
        if (city) {
            sql += "	c.district_id,\n"
        } else {
            sql += "	c.city_id,\n"
        }
        sql += "	COUNT ( * ) \n" +
                "FROM\n" +
                "	collateral\n" +
                "	C LEFT JOIN estate e ON C.estate_id = e.ID \n" +
                "WHERE\n" +
                "	building_type_id = ${buildtype?.id} \n"
        if (city) {
            sql += "	AND C.city_id = ${city?.id} \n"
        } else {
            sql += "	AND C.province_id = ${province?.id} \n"
        }
        if (bank) {
            sql += "	AND C.account_id = ${bank?.id} \n"
        }
        sql += "	AND house_nature_id = ${secondHouse?.id} \n" +
                "GROUP BY\n"
        if (city) {
            sql += "	C.district_id \n"
        } else {
            sql += "	C.city_id \n"
        }
        sql += "	) t2 "
        if (city) {
            sql += "ON t1.district_id = t2.district_id"
        } else {
            sql += "ON t1.city_id = t2.city_id"
        }

        println sql
        def db = new groovy.sql.Sql(dataSource)
        def result = db.rows(sql).asList()
        render([result: result] as JSON)
    }


    /**
     * 商业用房押品分布
     * @return
     */
    def businessEstate() {
        def bank = Account.findById(params?.bank)
        def city = City.findByZoneNumber(params?.city)
        def province = Province.findByName("广东省")
        def buildtype = BuildingTypeDict.findByCode("Business")
        def sql = "SELECT \n"

        if (city) {
            sql += "( SELECT NAME FROM area WHERE area_id = c.district_id ) AS distrcit_name,"
        } else {
            sql += "( SELECT NAME FROM area WHERE area_id = c.city_id ) AS distrcit_name,"
        }
        sql += "	COUNT ( * ) \n" +
                "FROM\n" +
                "	collateral\n" +
                "	C LEFT JOIN estate e ON C.estate_id = e.ID \n" +
                "WHERE\n" +
                "	building_type_id = ${buildtype?.id} \n"
        if (city) {
            sql += "	AND C.city_id = ${city?.id} \n"
        } else {
            sql += "	AND C.province_id = ${province?.id} \n"
        }

        if (bank) {
            sql += "	AND C.account_id = ${bank?.id} \n"
        }

        if (city) {
            sql += "	GROUP BY  C.district_id \n"
        } else {
            sql += "   GROUP BY	C.city_id \n"
        }

        println sql
        def db = new groovy.sql.Sql(dataSource)
        def result = db.rows(sql).asList()
        render([result: result] as JSON)

    }
//    住宅价格及价差
    @Transactional
    def priceVariance() {

        def bank = Account.findById(params?.bank)
        def city = City.findByZoneNumber(params?.city)
        def province = Province.findByName("广东省")
        def buildtypeResidential = BuildingTypeDict.findByCode("Residential")
        def fistHouse = HouseNatureDict.findByCode("FistHouse")
        def secondHouse = HouseNatureDict.findByCode("SecondHouse")

        def query = Estate.executeQuery("select to_char( e.startDate, 'yyyy' ) as date1 from Estate e where to_char( " +
                "e.startDate, 'yyyy' )>='2010' and to_char( e.startDate, 'yyyy' )<='2018' group by" +
                " to_char( e.startDate, 'yyyy' ) ")

        def resultList = []

        query.each {
            if (it) {
                def sql = "SELECT\n"
                if (city) {
                    sql += "( SELECT NAME FROM area WHERE area_id = t1.district_id ) AS distrcit_name,"
                } else {
                    sql += "( SELECT NAME FROM area WHERE area_id = t1.city_id ) AS distrcit_name,"
                }
                sql += "	t1.price1,\n" +
                        "	t2.price2,\n" +
                        "	t1.price1 - t2.price2 as dvalue,\n" +
                        "	t1.date1 as year \n" +
                        "FROM\n" +
                        "	(\n" +
                        "SELECT\n" +
                        "	avg(e.unit_price) price1,\n"
                if (city) {
                    sql += "	C.district_id ,\n"
                } else {
                    sql += "	C.city_id ,\n"
                }
                sql += "	to_char(e.start_date,'yyyy') date1\n" +
                        "FROM\n" +
                        "	collateral\n" +
                        "	C LEFT JOIN estate e ON C.estate_id = e.ID \n" +
                        "WHERE\n" +
                        "	e.building_type_id = ${buildtypeResidential.id}  \n" +
                        "	AND house_nature_id = ${fistHouse.id} \n" +
                        "	and to_char(e.start_date,'yyyy') ='${it}'\n"
                if (city) {
                    sql += "and c.city_id=${city.id}"
                } else {
                    sql += "and c.province_id=${province.id}"
                }
                if (bank) {
                    sql += " and c.account_id=${bank.id}"
                }
                if (city) {
                    sql += " group by c.district_id "
                } else {
                    sql += " group by  c.city_id "
                }
                sql += ",date1\n" +
                        "	) t1\n" +
                        "	LEFT JOIN (\n" +
                        "SELECT\n" +
                        "	avg(e.unit_price) price2,\n"
                if (city) {
                    sql += "	C.district_id ,\n"
                } else {
                    sql += "	C.city_id ,\n"
                }
                sql += "	to_char(e.start_date,'yyyy') date1\n" +
                        "FROM\n" +
                        "	collateral\n" +
                        "	C LEFT JOIN estate e ON C.estate_id = e.ID \n" +
                        "WHERE\n" +
                        "	e.building_type_id = ${buildtypeResidential.id} \n" +
                        "	AND house_nature_id = ${secondHouse.id} \n"
                if (city) {
                    sql += "	AND C.city_id = ${city?.id} \n"
                } else {
                    sql += "	AND C.province_id = ${province?.id} \n"
                }
                if (bank) {
                    sql += " and c.account_id=${bank.id}"
                }
                sql += "	and to_char(e.start_date,'yyyy') ='${it}'\n" +
                        "	group by"
                if (city) {
                    sql += " c.district_id "
                } else {
                    sql += " c.city_id "
                }
                sql += ",date1\n" +
                        "	) t2 ON "
                if (city) {
                    sql += "t1.district_id = t2.district_id"
                } else {
                    sql += "t1.city_id = t2.city_id "
                }

                println sql
                def db = new groovy.sql.Sql(dataSource)
                def result = db.rows(sql).asList()

                def map = [year: it, cltvList: result]
                resultList.add(map)
            }

        }


        render([resultList: resultList] as JSON)
    }

    /**
     * 开发商监测
     */
    def developerMonitor() {
        def bank = params?.bank
        def city = params?.city

    }

    /**
     * 偿债收入比
     */
    def debtEarningRate() {
        def bank = Account.findById(params?.bank)
        def city = City.findByZoneNumber(params?.city)
        def province = Province.findByName("广东省")
        def sql = "SELECT\n"
        if (city) {
            sql += "	( SELECT NAME FROM area WHERE area_id = c.district_id ) AS distrcit_name,\n"
        } else {
            sql += "	( SELECT NAME FROM area WHERE area_id = c.city_id ) AS distrcit_name,\n"
        }
        sql += "	SUM ( m.monthly_repayment_amount) / " +
                "SUM (  co.monthly_income ) as rate\n" +
                " FROM  " +
                "	collateral\n" +
                "	C LEFT JOIN estate e ON C.estate_id = e.\n" +
                "	ID LEFT JOIN mortgage M ON e.mortgage_id = M.ID " +
                " left join  contact co on m.mortgage_contact_id=co.id\n" +
                "WHERE\n"
        if (bank) {
            sql += "	and C.account_id = ${bank?.id}  \n"
        }
        if (city) {
            sql += "	C.city_id = ${city?.id} GROUP BY C.district_id\n"
        } else {
            sql += "	C.province_id = ${province?.id} GROUP BY C.city_id \n"
        }


        println sql
        def db = new groovy.sql.Sql(dataSource)
        def debtEarningResult = db.rows(sql).asList()


        def ageSql = "select \n" +
                "	sum(case when (cast(to_char(AGE(co.birthday),'yyyy')as integer)>=20 and cast(to_char(AGE(co.birthday),'yyyy')as integer)<30 ) then 1 else 0 end  ) \n" +
                "	as \"20-29岁\",\n" +
                "	sum(case when (cast(to_char(AGE(co.birthday),'yyyy')as integer)>=30 and cast(to_char(AGE(co.birthday),'yyyy')as integer)<40 ) then 1 else 0 end  ) \n" +
                "	as \"30-39岁\",\n" +
                "	sum(case when (cast(to_char(AGE(co.birthday),'yyyy')as integer)>=40 and cast(to_char(AGE(co.birthday),'yyyy')as integer)<50 ) then 1 else 0 end  ) \n" +
                "	as \"40-49岁\",\n" +
                "	sum(case when (cast(to_char(AGE(co.birthday),'yyyy')as integer)>=50 and cast(to_char(AGE(co.birthday),'yyyy')as integer)<60 ) then 1 else 0 end  ) \n" +
                "	as \"50-59岁\",\n"
        if (city) {
            ageSql += "	( SELECT NAME FROM area WHERE area_id = c.district_id ) AS distrcit_name \n"
        } else {
            ageSql += "	( SELECT NAME FROM area WHERE area_id = c.city_id ) AS distrcit_name \n"
        }
        ageSql += "	from \n" +
                "		collateral\n" +
                "	C LEFT JOIN estate e ON C.estate_id = e.\n" +
                "	ID LEFT JOIN mortgage M ON e.mortgage_id = M.ID \n" +
                "	left join contact co on m.mortgage_contact_id=co.id \n" +
                "	WHERE\n"
        if (bank) {
            ageSql += "	and C.account_id = ${bank?.id}  \n"
        }
        if (city) {
            ageSql += "	C.city_id = ${city?.id} GROUP BY C.district_id\n"
        } else {
            ageSql += "	C.province_id = ${province?.id} GROUP BY C.city_id \n"
        }
        println ageSql
        def ageResult = db.rows(ageSql).asList()

        render([result: [ageResult:ageResult,debtEarningResult:debtEarningResult]] as JSON)

    }

    /**
     * 商业用房监测
     * 1.商业用房出租面积占比=出租面积/总面积
     * 2.租金回笼情况=（出租单价*出租成交面积*贷款年限）/贷款总额
     */
    def businessBuildMonitor() {
        def bank = Account.findById(params?.bank)
        def city = City.findByZoneNumber(params?.city)
        def province = Province.findByName("广东省")
        def buildtypeBusiness = BuildingTypeDict.findByCode("Business")


        def sql = "SELECT\n"
        if (city) {
            sql += "( SELECT NAME FROM area WHERE area_id = c.district_id ) AS distrcit_name,"
        } else {
            sql += "( SELECT NAME FROM area WHERE area_id = c.city_id ) AS distrcit_name,"
        }
        sql += "	sum(rent_area ) rentarea,sum(actually_use_area) totalarea,\n" +
                "	sum(rent_area*rent_unit_price*loans_duration/12) backprice,sum(loans_amount) totalprice\n" +
                "FROM\n" +
                "	collateral\n" +
                "	C LEFT JOIN estate e ON C.estate_id = e.ID  where 1=1 \n"
        if (bank) {
            sql += "and c.account_id=${bank.id}"
        }
        if (city) {
            sql += "and c.city_id=${city.id} GROUP BY C.district_id"
        } else {
            sql += "and c.province_id=${province.id} GROUP BY C.city_id"
        }

        println sql
        def db = new groovy.sql.Sql(dataSource)
        def result = db.rows(sql).asList()
        result.each {
            def rentArea = it?.rentarea ? it?.rentarea : 0
            def totalArea = it?.totalarea ? it?.totalarea : 0
            def backPrice = it?.backprice ? it?.backprice : 0
            def totalPrice = it?.totalprice ? it?.totalprice : 0
            it.rentRate = 0
            it.backRate = 0
            if (totalArea && totalArea != 0) {
                it.rentRate = rentArea / totalArea
            }
            if (totalPrice && totalPrice != 0) {
                it.backRate = backPrice / totalPrice
            }


        }
        render([result: result] as JSON)

    }
    /**
     * 经营性物业贷款比例监测
     * 3.经营性物业贷款占比=经营性物业借款额/全量贷款总额
     */
    def commercialPropertyLoan() {
        def bank = Account.findById(params?.bank)
        def city = City.findByZoneNumber(params?.city)
        def province = Province.findByName("广东省")
        def sql = "SELECT\n"
        if (city) {
            sql += "	( SELECT NAME FROM area WHERE area_id = c.district_id ) AS distrcit_name,\n"
        } else {
            sql += "	( SELECT NAME FROM area WHERE area_id = c.city_id ) AS distrcit_name,\n"
        }
        sql += "	SUM ( CASE WHEN M.operating_property_loan in('个人经营性贷款' ) THEN M.loans_amount ELSE 0 END ) / " +
                "SUM (" +
                " M.loans_amount ) as rate\n" +
                "FROM\n" +
                "	collateral\n" +
                "	C LEFT JOIN estate e ON C.estate_id = e.\n" +
                "	ID LEFT JOIN mortgage M ON e.mortgage_id = M.ID \n" +
                "WHERE\n"
        if (bank) {
            sql += "	and C.account_id = ${bank?.id}  \n"
        }
        if (city) {
            sql += "	C.city_id = ${city?.id} GROUP BY C.district_id\n"
        } else {
            sql += "	C.province_id = ${province?.id} GROUP BY C.city_id \n"
        }


        println sql
        def db = new groovy.sql.Sql(dataSource)
        def result = db.rows(sql).asList()
        render([result: result] as JSON)


    }
    /**
     * 商业用房价格及租金指标
     */
    def businessRentPrice() {
        def bank = Account.findById(params?.bank)
        def city = City.findByZoneNumber(params?.city)
        def province = Province.findByName("广东省")
        def buildtypeBusiness = BuildingTypeDict.findByCode("Business")

        def query = Estate.executeQuery("select to_char( e.startDate, 'yyyy' ) as date1 from Estate e where to_char( " +
                "e.startDate, 'yyyy' )>='2010' and to_char( e.startDate, 'yyyy' )<='2018' group by" +
                " to_char( e.startDate, 'yyyy' ) ")

        def resultList = []

        query.each {
            def sql = "SELECT\n" +
                    "	avg(e.unit_price) price, avg( rent_unit_price) rentprice ,\n"
            if (city) {
                sql += "( SELECT NAME FROM area WHERE area_id = c.district_id ) AS distrcit_name,"
            } else {
                sql += "( SELECT NAME FROM area WHERE area_id = c.city_id ) AS distrcit_name,"
            }
            sql += "	to_char(e.start_date,'yyyy') date1\n" +
                    "FROM\n" +
                    "	collateral\n" +
                    "	C LEFT JOIN estate e ON C.estate_id = e.ID \n" +
                    "WHERE\n" +
                    "	e.building_type_id = ${buildtypeBusiness.id} \n" +
                    "	and to_char(e.start_date,'yyyy') ='${it}'\n"
            if (city) {
                sql += "and c.city_id=${city.id}"
            } else {
                sql += "and c.province_id=${province.id}"
            }
            if (bank) {
                sql += "and c.account_id=${bank.id}"
            }
            sql += "	group by date1,"
            if (city) {
                sql += " c.district_id "
            } else {
                sql += " c.city_id "
            }

            println sql
            def db = new groovy.sql.Sql(dataSource)
            def result = db.rows(sql).asList()

            def map = [year: it, priceList: result]
            resultList.add(map)

        }
        render([resultList: resultList] as JSON)


    }


    /**
     * 抵押率监测
     */
    //todo 修改SQL语句
    def cltvData() {
        def bank = Account.findById(params?.bank)
        def city = City.findByZoneNumber(params?.city)
        def province = Province.findByName("广东省")
        def buildtypeResidential = BuildingTypeDict.findByCode("Residential")
        def buildtypeBusiness = BuildingTypeDict.findByCode("Business")

        def query = Estate.executeQuery("select to_char( e.startDate, 'yyyy' ) as date1 from Estate e where to_char( " +
                "e.startDate, 'yyyy' )>='2010' and to_char( e.startDate, 'yyyy' )<='2018' group by" +
                " to_char( e.startDate, 'yyyy' ) ")
        println query
        def resultList = []

        query.each {
            if (it) {
                def sql = "SELECT t1.date1,\n"
                if (city) {
                    sql += "( SELECT NAME FROM area WHERE area_id = t1.district_id ) AS distrcit_name,"
                } else {
                    sql += "( SELECT NAME FROM area WHERE area_id = t1.city_id ) AS distrcit_name,"
                }
                sql += "	sum(case when t1.building_type_id=${buildtypeResidential.id} then t1.loans_balance else 1" +
                        " end ) " +
                        "/sum(case when t1" +
                        ".building_type_id=${buildtypeResidential.id} then t1.total_price else 1 end ) cltv1, \n" +
                        "	sum(case when t1.building_type_id=${buildtypeBusiness.id} then t1.loans_balance else 1 " +
                        "end )/sum(case when t1" +
                        ".building_type_id=${buildtypeBusiness.id} then t1.total_price else 1 end ) cltv2\n" +
                        "FROM\n" +
                        "	( SELECT to_char( e.start_date, 'yyyy' ) date1, e.loans_balance, e.total_price, e" +
                        ".building_type_id ,"
                if (city) {
                    sql += " c.district_id "
                } else {
                    sql += " c.city_id "
                }
                sql += "FROM	collateral\n" +
                        "	C LEFT JOIN estate e ON C.estate_id = e.ID where to_char( e.start_date, 'yyyy' )='${it}' \n"
                if (city) {
                    sql += "	AND C.city_id = ${city?.id} \n"
                } else {
                    sql += "	AND C.province_id = ${province?.id} \n"
                }
                if (bank) {
                    sql += "	AND C.account_id = ${bank?.id} \n"
                }
                sql += " ORDER BY date1 ) t1\n" +
                        "	group by t1.date1,"
                if (city) {
                    sql += "t1.district_id "
                } else {
                    sql += "t1.city_id "
                }

                println sql
                def db = new groovy.sql.Sql(dataSource)
                def result = db.rows(sql).asList()
                result.each {
                    it?.cltv1 = it?.cltv1 ? new Double(it?.cltv1) : 0
                    it?.cltv2 = it?.cltv2 ? new Double(it?.cltv2) : 0
                }
                def map = [year: it, cltvList: result]
                resultList.add(map)
            }

        }
        println resultList
        render([resultList: resultList] as JSON)
    }

    /**
     * 页面假数据
     * @return
     */
    def initData1() {
        def city = params?.city
        def cityList
        if (city) {
            cityList = Area.findAllByParentArea(Area.findByNameAndAreaLevel(city,2))
        } else {
            cityList = Area.findAllByParentAreaAndNameNotEqual(Area.findByName("广东省"), "深圳市")
        }

        def reslutList = []
        for (int i = 1; i < 10; i++) {

            def cltvList = []
            cityList.each {
                Random ra = new Random()
                def price1 = 20000 + (int) (Math.random() * ((50000 - 20000) + 1))
                def price2 = 10000 + (int) (Math.random() * ((40000 - 10000) + 1))
                def map = [distrcit_name: it?.name,
                           price1       : price1,
                           price2       : price2,
                           dvalue       : price1 - price2
                ]
                cltvList.add(map)


            }
            reslutList.add(cltvList: cltvList, year: 2009 + i)

        }
        render([resultList: reslutList] as JSON)
    }

    def initData2() {
        def city = params?.city
        def cityList
        if (city) {
            cityList = Area.findAllByParentArea(Area.findByNameAndAreaLevel(city,2))
        } else {
            cityList = Area.findAllByParentAreaAndNameNotEqual(Area.findByName("广东省"), "深圳市")
        }

        def reslutList = []
        for (int i = 1; i < 10; i++) {

            def priceList = []
            cityList.each {
                Random ra = new Random()
                def price = 20000 + (int) (Math.random() * ((50000 - 20000) + 1))
                def rentprice = 10000 + (int) (Math.random() * ((40000 - 10000) + 1))
                def map = [distrcit_name: it?.name,
                           price        : price,
                           rentprice    : rentprice
                ]
                priceList.add(map)


            }
            reslutList.add(priceList: priceList, year: 2009 + i)

        }
        render([resultList: reslutList] as JSON)
    }

    def initData3() {
        def city = params?.city
        def cityList
        if (city) {
            cityList = Area.findAllByParentArea(Area.findByNameAndAreaLevel(city,2))
        } else {
            cityList = Area.findAllByParentAreaAndNameNotEqual(Area.findByName("广东省"), "深圳市")
        }

        def reslutList = []
        cityList.each {
            Random ra = new Random()
            def rentRate = 50 + (int) (Math.random() * ((100 - 50) + 1))
            def backRate = 40 + (int) (Math.random() * ((100 - 40) + 1))
            def map = [distrcit_name: it?.name,
                       rentRate     : rentRate,
                       backRate     : backRate
            ]
            reslutList.add(map)


        }

        render([result: reslutList] as JSON)
    }


    /**
     * 抵押率监测假数据
     * @return
     */
    def initData4() {
        def city = params?.city
        def cityList
        if (city) {
            cityList = Area.findAllByParentArea(Area.findByNameAndAreaLevel(city,2))
        } else {
            cityList = Area.findAllByParentAreaAndNameNotEqual(Area.findByName("广东省"), "深圳市")
        }

        def reslutList = []
        for (int i = 1; i < 10; i++) {

            def cltvList = []
            cityList.each {
                Random ra = new Random()
                def cltv1 = (50 + (int) (Math.random() * ((95 - 50) + 1))) / 100
                def cltv2 = (50 + (int) (Math.random() * ((95 - 50) + 1))) / 100
                def map = [distrcit_name: it?.name,
                           cltv1        : cltv1,
                           cltv2        : cltv2
                ]
                cltvList.add(map)


            }
            reslutList.add(cltvList: cltvList, year: 2009 + i)

        }
        render([resultList: reslutList] as JSON)
    }

    /**
     * 舆情数据
     */

    def initData5() {
        def list = ["泥石流", "火灾", "水灾", "罚款", "违约", "地震", "台风", "倒塌", "官司", "坐牢", "赔偿", "逾期", "倒闭", "资金断链", "罚金"]

        def reslutList = []
        for (int i = 1; i < 10; i++) {

            def priceList = []
            list.each {
                Random ra = new Random()
                def count = 50 + (int) (Math.random() * ((150 - 50) + 1))
                def map = [name : it,
                           count: count,
                ]
                priceList.add(map)
            }

            reslutList.add(COUNTLIST: priceList, YEARQUARTER: 2009 + i)

        }
        render([resultList: reslutList] as JSON)
    }

    def report() {}

    def accountShow() {}

    def communityShow() {}

    def developer() {}

    def developerShow() {}

    def monitor() {}

    def riskWarning() {}

    def bankingStressAnalyze() {}

}


